package org.budo.support.javax.sql.util;

import java.io.Closeable;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.budo.support.lang.util.BeanUtil;
import org.budo.support.lang.util.MapUtil;
import org.budo.support.lang.util.StringUtil;

import lombok.extern.slf4j.Slf4j;

/**
 * @author lmw
 * @see org.budo.jdbc.dao.BudoJdbcDao
 */
@Slf4j
public class JdbcUtil {
    private static String insertSql(String table, List<Map<String, Object>> rows) {
        List<String> columns = MapUtil.keys(rows);

        List<String> values = new ArrayList<String>();
        for (Map<String, Object> row : rows) {
            String join = " ( " + StringUtil.join(values(columns, row)) + " ) ";
            values.add(join);
        }

        return "INSERT INTO " + table + " (" + StringUtil.join(columns, ", ", "`", "`") + ") VALUES " + StringUtil.join(values) + " ";
    }

    private static List<String> values(List<String> keys, Map<String, Object> row) {
        List<String> vals = new ArrayList<String>();
        for (String key : keys) {
            String val = "'" + row.get(key) + "'";
            vals.add(val);
        }
        return vals;
    }

    private static List<String> columnDefinition(List<Map<String, Object>> rows, String type) {
        List<String> columns = new ArrayList<String>();

        List<String> keys = MapUtil.keys(rows);

        for (String key : keys) {
            columns.add("`" + key + "` " + type);
        }

        return columns;
    }

    public static void writeToTable(DataSource dataSource, String table, List<Map<String, Object>> rows) {
        // 建表
        List<String> columnDefinition = columnDefinition(rows, "varchar(25500)");
        String createTable = "CREATE TABLE IF NOT EXISTS " + table + " (" + StringUtil.join(columnDefinition) + ")";
        JdbcUtil.executeUpdate(dataSource, createTable);

        // 写数据
        String insertSql = insertSql(table, rows);
        JdbcUtil.executeUpdate(dataSource, insertSql);
    }

    public static List<Map<String, Object>> executeQuery(Connection connection, String sql) {
        return executeQuery(connection, sql, null);
    }

    public static List<Map<String, Object>> executeQuery(Connection connection, String sql, Object[] parameters) {
        return executeQuery(connection, sql, parameters, true);
    }

    public static Integer executeUpdate(Connection connection, String sql, Object[] parameters, Boolean closeAfterQuery) {
        PreparedStatement preparedStatement = prepareStatement(connection, sql);
        setParameters(preparedStatement, parameters);

        try {
            return preparedStatement.executeUpdate();
        } catch (Throwable e) {
            throw new ExecuteUpdateRuntimeException("#48 jdbc.executeUpdate error, sql=" + sql //
                    + ", parameters=" + Arrays.toString(parameters) + ", connection=" + connection //
                    + ", e=" + e, e);
        } finally {
            close(preparedStatement);

            if (closeAfterQuery) {
                close(connection);
            }
        }
    }

    public static Integer executeUpdate(Connection connection, String sql, Object[] parameters) {
        return executeUpdate(connection, sql, parameters, true);
    }

    public static List<Map<String, Object>> executeQuery(Connection connection, String sql, Object[] parameters, Boolean closeAfterQuery) {
        PreparedStatement preparedStatement = prepareStatement(connection, sql);
        setParameters(preparedStatement, parameters);

        try {
            ResultSet resultSet = preparedStatement.executeQuery();
            return ResultSetUtil.toMapList(resultSet); // 读数据后关闭
        } catch (Throwable e) {
            throw new RuntimeException("#34 sql=" + sql + ", parameters=" + Arrays.toString(parameters) + ", connection=" + connection + ", e=" + e, e);
        } finally {
            close(preparedStatement);

            if (closeAfterQuery) {
                close(connection);
            }
        }
    }

    public static List<Map<String, Object>> executeQuery(DataSource dataSource, String sql, Object[] parameters) {
        Connection connection = getConnection(dataSource);

        return executeQuery(connection, sql, parameters);
    }

    public static <T> T executeFind(DataSource dataSource, String sql, Object[] parameters, Class<T> type) {
        List<T> list = executeQuery(dataSource, sql, parameters, type);
        return null == list || list.isEmpty() ? null : list.get(0);
    }

    public static <T> T executeFind(Connection connection, String sql, Object[] parameters, Class<T> type) {
        List<Map<String, Object>> maps = executeQuery(connection, sql, parameters);
        List<T> list = (List<T>) BeanUtil.listToEntity(maps, type);
        return null == list || list.isEmpty() ? null : list.get(0);
    }

    public static <T> T executeFind(Connection connection, String sql, Object[] parameters, Class<T> type, Boolean closeAfterQuery) {
        List<Map<String, Object>> maps = executeQuery(connection, sql, parameters, closeAfterQuery);
        List<T> list = (List<T>) BeanUtil.listToEntity(maps, type);
        return null == list || list.isEmpty() ? null : list.get(0);
    }

    public static <T> List<T> executeQuery(DataSource dataSource, String sql, Object[] parameters, Class<T> type) {
        List<Map<String, Object>> maps = executeQuery(dataSource, sql, parameters);
        return BeanUtil.listToEntity(maps, type);
    }

    public static Integer executeUpdate(DataSource dataSource, String sql) {
        return executeUpdate(dataSource, sql, null);
    }

    public static Integer executeUpdate(DataSource dataSource, String sql, Object[] parameters) {
        Connection connection = getConnection(dataSource);

        return executeUpdate(connection, sql, parameters);
    }

    public static void checkConnectMysql(String url, String username, String password) {
        log.info("#80 checkConnectMysql, url=" + url + ", username=" + username);

        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            connection = getConnection(url, username, password);
            statement = connection.createStatement();
            resultSet = statement.executeQuery("SELECT 1");
        } catch (Throwable e) {
            throw new RuntimeException("#36 mysql not ok, url=" + url + ", username=" + username + ", e=" + e, e);
        } finally {
            close(resultSet);
            close(statement);
            close(connection);
        }
    }

    /**
     * 连接数据库并执行 SELECT 1, 失败时报错
     */
    public static void checkConnectMysql(String host, Integer port, String username, String password) {
        String url = "jdbc:mysql://" + host + ":" + port + "?connectTimeout=2000&socketTimeout=2000";
        checkConnectMysql(url, username, password);
    }

    public static Connection getConnection(String url, String username, String password) {
        try {
            DriverManager.setLoginTimeout(2);
            return DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            throw new RuntimeException("#106 e=" + e + ", url=" + url + ", username=" + username, e);
        }
    }

    public static Connection getConnection(DataSource dataSource) {
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public static PreparedStatement prepareStatement(Connection connection, String sql) {
        try {
            return connection.prepareStatement(sql);
        } catch (SQLException e) {
            throw new RuntimeException("#73 sql=" + sql + ", connection=" + connection + ", e=" + e, e);
        }
    }

    public static ResultSet executeQuery(PreparedStatement preparedStatement) {
        try {
            return preparedStatement.executeQuery();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public static Integer executeUpdate(PreparedStatement preparedStatement) {
        try {
            return preparedStatement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public static PreparedStatement setParameters(PreparedStatement preparedStatement, Object[] parameters) {
        if (null == preparedStatement) {
            log.error("#53 preparedStatement=" + preparedStatement + ", parameters=" + parameters);
            return preparedStatement;
        }

        if (null == parameters || parameters.length < 1) {
            return preparedStatement;
        }

        for (int i = 0; i < parameters.length; i++) {
            int parameterIndex = i + 1;
            Object parameterValue = parameters[i];
            setParameter(preparedStatement, parameterIndex, parameterValue);
        }

        return preparedStatement;
    }

    private static void setParameter(PreparedStatement preparedStatement, Integer parameterIndex, Object parameterValue) {
        try {
            preparedStatement.setObject(parameterIndex, parameterValue);
        } catch (SQLException e) {
            throw new RuntimeException("#57 preparedStatement=" + preparedStatement + ", parameterIndex=" + parameterIndex + ", parameterValue=" + parameterValue + ", e=" + e, e);
        }
    }

    public static void close(Statement statement) {
        if (null == statement) {
            log.error("#94 close, statement=" + statement);
            return;
        }

        try {
            statement.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public static void closeQuietly(Statement statement) {
        if (null == statement) {
            log.error("#94 close, statement=" + statement);
            return;
        }

        try {
            statement.close();
        } catch (SQLException e) {
            log.error("#118 closeQuietly error, e=" + e, e);
        }
    }

    public static void close(Connection connection) {
        if (null == connection) {
            log.error("#107 close, connection=" + connection);
            return;
        }

        try {
            connection.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public static void close(ResultSet resultSet) {
        if (null == resultSet) {
            log.error("#120 close, resultSet=" + resultSet);
            return;
        }

        try {
            resultSet.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public static String getHost(String url) {
        int a = url.indexOf("://");
        if (a <= 0) {
            throw new IllegalArgumentException("#206 url=" + url);
        }

        int b = url.indexOf(':', a + 3);
        if (b <= 0) {
            b = url.indexOf('/', a + 3);
        }

        if (b <= 0) {
            b = url.indexOf('?', a + 3);
        }

        if (b <= 0) {
            b = url.length();
        }

        return url.substring(a + 3, b);
    }

    public static Integer getPort(String url) {
        String host = getHost(url);

        int a = url.indexOf("://" + host + ":");
        if (a <= 0) {
            return 3306;
        }

        int b = url.indexOf('/', a + 4 + host.length());
        if (b <= 0) {
            b = url.length();
        }

        String port = url.substring(a + 4 + host.length(), b);
        return Integer.valueOf(port);
    }

    public static void closeQuietly(DataSource dataSource) {
        if (null == dataSource) {
            log.error("#263 dataSource=" + dataSource);
            return;
        }

        if (!(dataSource instanceof Closeable)) {
            log.error("#270 not instanceof Closeable, type=" + dataSource.getClass() + ", dataSource=" + dataSource);
            return;
        }

        try {
            ((Closeable) dataSource).close();
        } catch (Throwable e) {
            log.error("#272 close error, e=" + e, e);
        }
    }

    public static List<Map<String, Object>> executeQuery(DataSource dataSource, String sql) {
        Connection connection = getConnection(dataSource);
        return executeQuery(connection, sql);
    }

    public static boolean checkConnectMysql_2(String host, Integer port, String username, String password) {
        try {
            checkConnectMysql(host, port, username, password);
            return true;
        } catch (Throwable e) {
            log.error("#288 checkConnectMysql_2, host=" + host + ", port=" + port + ", username=" + username, e);
            return false;
        }
    }

    public static Integer executeUpdate(Connection connection, String sql) {
        return executeUpdate(connection, sql, null);
    }

    public static Boolean isTableNotExist(Throwable e) {
        String error = "" + e;
        return error.contains("Table") && error.contains("doesn't exist"); // MySQLSyntaxErrorException: Table 'tjk_dw.tj_warning_client_system_msg'doesn't
                                                                           // exist
    }

    public static Boolean isIncorrectStringValue(Throwable e) {
        String error = "" + e;
        return error.contains("SQLException: Incorrect string value:"); // SQLException: Incorrect string value: '\xF0\x9F\x91\x8C' for
                                                                        // column'msgContent' at row 1
    }

    public static Boolean isDataTooLong(Throwable e) {
        String error = "" + e;
        return error.contains("MysqlDataTruncation: Data truncation: Data too long for column"); // MysqlDataTruncation: Data truncation: Data too long for column 'open_store'at
                                                                                                 // row 1
    }

    public static List<Map<String, Object>> getTables(DataSource dataSource) {
        Connection connection = null;

        try {
            connection = JdbcUtil.getConnection(dataSource);

            DatabaseMetaData metaData = connection.getMetaData();

            ResultSet tableResultSet = metaData.getTables(null, null, null, null);
            List<Map<String, Object>> tableMapList = ResultSetUtil.toMapList(tableResultSet);

            return tableMapList;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            close(connection);
        }
    }

    public static <T> T executeFind(DataSource dataSource, String sql, Class<T> type) {
        return executeFind(dataSource, sql, null, type);
    }

    public static void commitAndClose(Connection connection) {
        commit(connection);

        close(connection);
    }

    private static void commit(Connection connection) {
        if (null == connection) {
            log.error("#416 commitAndClose, connection=" + connection);
            return;
        }

        try {
            if (connection.getAutoCommit()) {
                return;
            }

            connection.commit();
        } catch (SQLException e) {
            log.error("#418 e=" + e, e);
        }
    }
}